package com.bishe.utils;


import cn.hutool.core.bean.BeanUtil;
import lombok.val;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;

import java.util.*;


/**
 * sql查询工具类
 * @date 2022/3/2 16:28
 */
public class SqlQueryUtils<T> {

    //sql
    private StringBuffer sql;

    //数据
    private T res;

    //参数
    private List<Object> param;

    //执行查询jdbc模板
    private JdbcTemplate jdbcTemplate;

    /**
     * 生成sql
     *
     * @param clazz
     */
    public SqlQueryUtils(Class<T> clazz, JdbcTemplate jdbcTemplate) {
        try {
            this.jdbcTemplate = jdbcTemplate;
            this.sql = new StringBuffer();
            //实例化数据
            this.res = clazz.newInstance();
            String sql = "select * from %s where 1=1";
            //获取实体类名称
            String name = this.res.getClass().getSimpleName();
            this.sql.append(String.format(sql, name));

            param = new ArrayList<>();
        } catch (Exception e) {
            throw new EcoBootException(e.getMessage());
        }
    }

    public List<Object> getParam() {
        return this.param;
    }

    /**
     * 获取sql
     *
     * @return
     */
    public String getSql() {
        return this.sql.toString();
    }

    public void  setSql(String sql){
        this.sql = new StringBuffer().append(sql);
    }

    /**
     * 等于
     *
     * @param field
     * @param val
     * @return
     */
    public SqlQueryUtils<T> eq(String field, Object val) {
        param.add(val);
        String ss = " and  %s = ? ";
        this.sql.append(String.format(ss, field));
        return this;
    }

    /**
     * 等于
     *
     * @param fn
     * @param val
     * @return
     */
    public SqlQueryUtils<T> eq(ColumnUtil.SFunction<T, ?> fn, Object val) {
        String fieldName = ColumnUtil.getFieldName(fn);

        return this.eq(fieldName, val);
    }

    /**
     * 模糊
     *
     * @param field
     * @param val
     * @return
     */
    public SqlQueryUtils<T> like(String field, Object val) {
        param.add("%" + val + "%");
        String ss = " and  %s like ? ";
        this.sql.append(String.format(ss, field));
        return this;
    }

    /**
     * 模糊
     *
     * @param val
     * @return
     */
    public SqlQueryUtils<T> like(ColumnUtil.SFunction<T, ?> fn, Object val) {
        String fieldName = ColumnUtil.getFieldName(fn);

        return this.like(fieldName, val);
    }

    /**
     * in 方法
     *
     * @param fn
     * @param val
     * @return
     */
    public SqlQueryUtils<T> in(ColumnUtil.SFunction<T, ?> fn, String... val) {
        String fieldName = ColumnUtil.getFieldName(fn);

        return this.in(fieldName, val);
    }

    /**
     * in 方法
     *
     * @param field
     * @param val
     * @return
     */
    public SqlQueryUtils<T> in(String field, String... val) {
        String ss = " and  %s in (%s)";
        ArrayList<String> objects = new ArrayList<>(val.length);
        for (int i = 0; i < val.length; i++) {
            objects.add(" ? ");
        }
        param.addAll(Arrays.asList(val));

        this.sql.append(String.format(ss, field, String.join(",", objects)));
        return this;
    }


    /**
     * 获取查询结果
     *
     * @return
     */
    public List<T> getList() {

        // JdbcTemplate jdbcTemplate = (JdbcTemplate) SpringContextUtil.getBean("jdbcTemplate");

        List<Map<String, Object>> maps = new ArrayList<>();
        try {
            maps = this.jdbcTemplate.queryForList(this.getSql(), this.param.toArray());
        } catch (EmptyResultDataAccessException e) {
            return new ArrayList<>();
        }

        ArrayList<T> ts = new ArrayList<>(maps.size());

        maps.forEach(s -> {
            T t = (T) BeanUtil.toBean(s, res.getClass());
            ts.add(t);
        });

        return ts;
    }

    /**
     * 获取查询结果
     *
     * @return
     */
    public T getOne() {
        //JdbcTemplate jdbcTemplate = (JdbcTemplate) SpringContextUtil.getBean("jdbcTemplate");
        Map<String, Object> map = new HashMap<>();
        try {
            map = this.jdbcTemplate.queryForMap(this.getSql(), this.param.toArray());
        } catch (EmptyResultDataAccessException e) {
            return null;
        }


        return (T) BeanUtil.toBean(map, res.getClass());
    }

    /**
     * 分页
     */
    public SqlQueryUtils<T> limit(int pageNo, int pageSize) {
        String ss = " limit ?,?";
        sql.append(ss);
        param.add((pageNo - 1) * pageSize);
        param.add(pageSize);
        return this;

    }

    /**
     * 大于
     *
     * @param field
     * @param val
     * @param isEqual 是否等于
     * @return
     */
    public SqlQueryUtils<T> maxThan(String field, Object val, Boolean isEqual) {
        String ss = " and %s >%s ?";
        if (isEqual) {
            sql.append(String.format(ss, field, "="));
        } else {
            sql.append(String.format(ss, field, ""));
            ;
        }
        param.add(val);
        return this;
    }

    /**
     * 小于
     *
     * @param field
     * @param val
     * @param isEqual 是否等于
     * @return
     */
    public SqlQueryUtils<T> minThan(String field, Object val, Boolean isEqual) {
        String ss = " and %s <%s ?";
        if (isEqual) {
            sql.append(String.format(ss, field, "="));
        } else {
            sql.append(String.format(ss, field, ""));
            ;
        }
        param.add(val);
        return this;
    }

    /**
     * 大于
     *
     * @param fn
     * @param val
     * @return
     */
    public SqlQueryUtils<T> maxThan(ColumnUtil.SFunction<T, ?> fn, Object val, Boolean isEqual) {
        String fieldName = ColumnUtil.getFieldName(fn);

        return this.maxThan(fieldName, val, isEqual);
    }

    /**
     * 大于
     *
     * @param fn
     * @param val
     * @return
     */
    public SqlQueryUtils<T> minThan(ColumnUtil.SFunction<T, ?> fn, Object val, Boolean isEqual) {
        String fieldName = ColumnUtil.getFieldName(fn);

        return this.minThan(fieldName, val, isEqual);
    }
    /**
     * 排序正序
     * @param fn
     * @return
     */
    public SqlQueryUtils<T> orderBy(ColumnUtil.SFunction<T, ?> fn) {
        String fieldName = ColumnUtil.getFieldName(fn);

        return this.orderBy(fieldName);
    }
    /**
     * 正序排序
     * @param fieldName
     * @return
     */
    public SqlQueryUtils<T> orderBy(String fieldName) {
        String ss =  " order by %s";
        this.sql.append(String.format(ss,fieldName));
        return this;
    }


    /**
     * 倒叙排序
     * @param fieldName
     * @return
     */
    public SqlQueryUtils<T> orderByDesc(String fieldName) {
        String ss =  " order by %s desc";
        this.sql.append(String.format(ss,fieldName));
        return this;
    }

    /**
     * 倒叙排序
     * @return
     */
    public SqlQueryUtils<T> orderByDesc(ColumnUtil.SFunction<T, ?> fn) {
        String fieldName = ColumnUtil.getFieldName(fn);
        return this.orderByDesc(fieldName);
    }

    /**
     * 获取数量
     */
    public Integer getCount() {
        String sql = this.getSql();
        sql  = sql.replace("*", "count(*)");
        return this.jdbcTemplate.queryForObject(sql, Integer.class, this.param.toArray());
    }
}
